package org.millioncall.yueyoga.admin.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.millioncall.openplatform.util.DateTimeUtils;
import org.millioncall.yueyoga.admin.dao.MemberStatDAO;
import org.millioncall.yueyoga.admin.service.MemberStatService;

/**
 * 会员统计业务类实现类
 * 
 * @author zangjinyu
 * @version 1.0.0
 * @since 1.0.0
 * 
 */
public class MemberStatServiceImpl implements MemberStatService {
	private MemberStatDAO memberStatDAO;

	public void setMemberStatDAO(MemberStatDAO memberStatDAO) {
		this.memberStatDAO = memberStatDAO;
	}

	/**
	 * 简单实现，复杂实现包括表连接，having 条件，where 条件
	 */
	@Override
	public List<Object[]> memberStatQuery(List<String> leftGroupBy,
			List<String> topGroupBy, String statCol, String function) {
		String selectStr = "select";
		String blankStr = " ";
		String commaStr = ",";
		String groupByStr = " group by";
		// --关联表查询
		String joinStr = " ";
		if (leftGroupBy != null && !leftGroupBy.isEmpty()) {
			for (String str : leftGroupBy) {
				Map<String, String> map = relatedSQL(str);
				String strTemp = str;
				str = map.get("str");
				joinStr += map.get("joinStr");
				// --如果是年龄分组
				if ("birthday".equals(strTemp)) {
					groupByStr += blankStr + " t1.nld " + commaStr;
				} else {
					groupByStr += blankStr + str + commaStr;
				}
				selectStr += blankStr + str + commaStr;
			}
		}

		if (topGroupBy != null && !topGroupBy.isEmpty())
			for (String str : topGroupBy) {
				Map<String, String> map = relatedSQL(str);
				String strTemp = str;
				str = map.get("str");
				joinStr += map.get("joinStr");
				// --如果是年龄分组
				if ("birthday".equals(strTemp)) {
					groupByStr += blankStr + " t1.nld " + commaStr;
				} else {
					groupByStr += blankStr + str + commaStr;
				}
				selectStr += blankStr + str + commaStr;
			}

		groupByStr = groupByStr.substring(0, groupByStr.length() - 1);

		// --统计数量
		if ("number".equals(statCol)) {
			statCol = " m.memberId ";
		}
		// --只有当统计的列为积分或者余额时，才进行统计
		else if ("score".equals(statCol) || "remaining".equals(statCol)) {
			joinStr += " inner join yueyoga_remaining r on m.memberId = r.memberId ";
		}

		selectStr += function + "(" + statCol + ")";

		String tableName = "yueyoga_member m ";

		String sql = selectStr + " from " + tableName + joinStr + groupByStr;

		List<Object[]> results = this.memberStatDAO.memberStat(sql,
				new HashMap<String, Object>());
		return results;
	}

	// --关联sql语句
	public Map<String, String> relatedSQL(String str) {
		Map<String, String> map = new HashMap<String, String>();
		String joinStr = "";
		// --信息来源
		if ("marketingSource".equals(str)) {
			str = " xxly.name ";
			joinStr = "left join yueyoga_status xxly on xxly.id = m.marketingSource ";
			
			// --状态
		} else if ("status".equals(str)) {
			str = " zt.name ";
			joinStr = "left join yueyoga_status zt on zt.code = (select zzt.parentCode from yueyoga_status zzt where zzt.code = m.subStatus)";
			// --子状态
		} else if ("subStatus".equals(str)) {
			str = " zzt.name ";
			joinStr = "left join yueyoga_status zzt on zzt.code = m.subStatus ";
			// --渠道
		} else if ("marketingChannel".equals(str)) {
			str = " qd.name ";
			joinStr = "left join yueyoga_status qd on qd.code =(select zqd.parentCode from yueyoga_status zqd where zqd.id = m.subMarketingChannel) ";

			// --子渠道
		} else if ("subMarketingChannel".equals(str)) {
			str = " zqd.name ";
			joinStr = "left join yueyoga_status zqd on zqd.id = m.subMarketingChannel ";

			// --省份
		} else if ("provinceId".equals(str)) {
			str = " prve.name ";
			joinStr = "left join yueyoga_area prve on prve.areaId = m.provinceId ";

			// --市区
		} else if ("cityId".equals(str)) {
			str = " city.name ";
			joinStr = "left join yueyoga_area city on city.areaId = m.cityId ";

			// --小区
		} else if ("areaId".equals(str)) {
			str = " area.name ";
			joinStr = "left join yueyoga_area area on area.areaId = m.areaId ";

			// --地域
		} else if ("domainId".equals(str)) {
			str = " dq.name ";
			joinStr = "left join yueyoga_area dq on dq.areaId = m.domainId ";

			// --年龄
		} else if ("birthday".equals(str)) {
			Date date = new Date();
			String dt = DateTimeUtils.getInstance().format(date, "yyyy-MM-dd");
			// --mysql计算年龄
			str = " t1.nld ";
			joinStr += " inner join (select '<20' as nld,0 as min,20 as max ";
			joinStr += " UNION select '20-30',20,30 UNION select '30-40',30,40 ";
			joinStr += " UNION select '40-50',40,50 UNION select '>50',50,200 ) t1 on (";
			joinStr += " (date_format('" + dt
					+ "', '%Y') - date_format(m.birthday, '%Y')) >= min ";
			joinStr += " and (date_format('" + dt
					+ "', '%Y') - date_format(m.birthday, '%Y')) < max )";
		} else {
			str = "m." + str;
		}
		map.put("str", str);
		map.put("joinStr", joinStr);
		return map;
	}
}
